<?php
// Validate parameters.
if (!isset($_REQUEST['requestType'], $_REQUEST['id']) ||
		!is_numeric($_REQUEST['id'])) {
	die('validation failed');
}
//test comment

// Connect to the DB
require_once 'include/config.lib.php';
require_once 'include/database.lib.php';
$con = ConnectToDB();

$requestType = $_REQUEST['requestType'];
$id = $_REQUEST['id'];

if ($requestType == 'timechart') {
	$data = array();
	$readingReq = DBExecute("	SELECT COALESCE(datecreated, date) AS date, COALESCE(count, 0) AS count, a.*
														FROM (
															SELECT date, COUNT(*) AS count FROM resource AS r, reading AS read
															WHERE r.id=read.resourceid AND r.bibtexid=? GROUP BY date
														) FULL JOIN (
															SELECT a.id, datecreated, annofabid, personid, annotationbody
															FROM resource AS r, annotation AS a WHERE r.id=a.resourceid AND r.bibtexid=?
														) AS a ON datecreated=date ORDER BY date", array($id, $id));
	
	while ($row = $readingReq->fetchAssocRow()) {
		array_push($data,
			array('id' => $row['ID'],
						'personid' => $row['PERSONID'],
						'date' => $row['DATE'],
						'views' => $row['COUNT'],
						'title' => (empty($row['ANNOFABID'])) ? NULL : 'Review by ' . preg_replace('/-.*$/','',$row['ANNOFABID']),
						'desc' => (empty($row['ANNOFABID'])) ? NULL : preg_replace('/(.*[\n|\r]*.*<content>[\n|\r]*|\s*<\/content>[\n|\r]*.*)/','',$row['ANNOTATIONBODY'])
			)
		);
	}
			
} elseif ($requestType == 'timechartp') {
	$data = array();
	$readingReq = DBExecute("	SELECT COALESCE(date2, date) AS date, 
															COALESCE(numdate,0) AS count, fabid, body,
															resource.bibtexid AS rid,bibtex.title
														FROM (
															SELECT reading.date AS date, COUNT(reading.date) AS numdate
															FROM reading WHERE reading.personid=? GROUP BY reading.date
														) FULL JOIN (
													 		SELECT datecreated AS date2, resourceid AS fabid, annotationbody AS body
															FROM annotation WHERE personid=?
														) ON date2=date
														JOIN resource ON resource.id=fabid
														JOIN bibtex ON bibtex.id=resource.bibtexid
														ORDER BY date", array($id, $id));
	while ($row = $readingReq->fetchAssocRow()) {
		array_push($data,
			array('date' => $row['DATE'],
						'views' => $row['COUNT'],
						'title' => empty($row['TITLE']) ? (empty($row['FABID']) ? NULL : 'Review of ' . preg_replace('/-.*$/','',$row['FABID'])) : $row['TITLE'],
						'desc' => (empty($row['FABID'])) ? NULL : preg_replace('/(.*[\n|\r]*.*<content>[\n|\r]*|\s*<\/content>[\n|\r]*.*)/','',$row['BODY'])
			)
		);
	}

} elseif ($requestType == 'commentTree') {
	$stmt = DBExecute('SELECT b.*,t.snapshot FROM (SELECT a.* FROM annotation AS a, resource AS r WHERE r.id=a.resourceid AND r.bibtexid=?) AS b LEFT JOIN textsnapshots AS t ON t.id = b.textsnapshot', $id);
  $tosort = array();
	$authors = array();
	
  // Code to sort data until DB2 'ORDER BY' problem is resolved
	// Problem is due to ANNOTATIONBODY field too large to fit in temporary table for DB2
	// can be solved by increasing tmp table size or reduce ANNOTATIONBODY size.
  while($row = $stmt->fetchAssocRow()) {
  	array_push($tosort, $row);
  	$personid = $row['PERSONID'];
  	$name = preg_replace('/-.*$/','',$row['ANNOFABID']);
  	$author = array('PERSONID' => $personid, 'NAME' => $name);
  	if(!in_array($author,$authors)) {
  		array_push($authors, $author);
  	}
  }
  function cmp($a,$b) {
  	return ($a['REPLYTO'] < $b['REPLYTO']) ? -1 : 1;
  }
  usort($tosort,"cmp");
  
	// Construct comment tree.
  $tree = array();
  $foundParents = array();
	
  foreach ($tosort as $row) {
  	if ($row['REPLYTO']==null) {
  		$row['CHILDREN'] = array();
  		if ($row['HASREPLY']) {
  			$foundParents[$row['ID']] = &$row['CHILDREN'];
  		}
  		array_push($tree,$row);
  	} elseif (in_array($row['REPLYTO'],array_keys($foundParents))) {
  		$row['CHILDREN'] = array();
  		if ($row['HASREPLY']) {
  			$foundParents[$row['ID']] = &$row['CHILDREN'];
  		}
  		array_push($foundParents[$row['REPLYTO']],$row);
  	} else {
  		die('Tree algorithm incorrect');
  	}
  }
  $data = array("comments" => $tree, "authors" => $authors);

} elseif ($requestType == 'personnetwork') {
	$data = array();
	$adate = date('Y-m-d',strtotime("-30 days"));
	$row = DBExecute('SELECT username FROM person WHERE id=?',$id)->fetchAssocRow();
	$username = $row['USERNAME'];
	$personname = $row['USERNAME']."-%";
	$stmt = DBExecute("SELECT personid,annofabid,ratebyanno,datecreated 
										 FROM annotation,resource WHERE annotation.resourceid = resource.id
										 AND resource.bibtexid IS NOT NULL
										 AND personid 
										 IN (
										   SELECT id FROM person WHERE username IN (
										     SELECT SUBSTR(replytofabid,1,LOCATE('-',replytofabid)-1) AS other 
										     FROM annotation 
										     WHERE annofabid LIKE ? 
										     AND replytofabid IS NOT NULL 
										     AND replytofabid NOT LIKE ? 
										     UNION SELECT SUBSTR(annofabid,1,LOCATE('-',annofabid)-1) AS other 
										     FROM annotation 
										     WHERE replytofabid LIKE ? 
										     AND annofabid NOT LIKE ?
										   )
										 );",array($personname,$personname,$personname,$personname));
	
	$people = array();
	$people['_root_']=array('adjacencies'=>array(),'data'=>array('$personId'=>$id,'$color'=>'#FF0000','$type'=>'star'),'id'=>'_root_','name'=>$username);
	$previd = -1;
	$n=0;
	while ($row = $stmt->fetchAssocRow()) {
		$name = preg_replace('/-.*$/','',$row['ANNOFABID']);
		if(!$people[$name]) {
			$people[$name] = array("data" => array('$activeIndex' => 0, '$paperNums' => 0, '$positive' => 0,'$negative' => 0,'$neutral' => 0,'$personId' => $row['PERSONID']), 'id' => $name, 'name' => $name);
      $people[$name]['adjacencies'] = array();
    }
		array_push($people['_root_']['adjacencies'],array('nodeTo'=>$name,'nodeFrom'=>'_root_','data'=>array('$personId'=>$row['PERSONID'])));
		if($row['DATECREATED'] > $adate)
			$people[$name]['data']['$activeIndex']+=1;
		$people[$name]['data']['$paperNums']+=2;
		switch($row['RATEBYANNO']) {
			case 1:	$people[$name]['data']['$positive']++; break;
			case 2: $people[$name]['data']['$negative']++; break;
			case 3: $people[$name]['data']['$neutral']++; break;
			case 4: $people[$name]['data']['$positive']++; break;
		}
	}
	
	$stmt = DBExecute("SELECT person.username,COUNT(*) AS count
										 FROM person,reading 
										 WHERE person.username IN ('".implode("','",array_keys($people))."') 
										 AND reading.personid = person.id AND reading.date > ? GROUP BY person.username",$adate);
	while ($row = $stmt->fetchAssocRow()) {
		$people[$row['USERNAME']]['data']['$activeIndex'] += $row['COUNT'];
	}
	foreach($people as $a => $b) {
  	$b['adjacencies'] = array_values($b['adjacencies']);
  	array_push($data, $b);
  }

} elseif ($requestType == 'relationships') {
	$stmt = DBExecute('SELECT a.* FROM annotation AS a, resource AS r WHERE a.resourceid=r.id AND r.bibtexid=?', $id);  
  $data = array();
	$people = array();
	
  while($row = $stmt->fetchAssocRow()) {
  	$name = preg_replace('/-.*$/','',$row['ANNOFABID']);
  	if (!$people[$name]) {
  		$people[$name] = array("data" => array('$activeIndex' => 0, '$paperNums' => 0, '$positive' => 0,'$negative' => 0,'$neutral' => 0,'$personId' => $row['PERSONID']), 'id' => $name, 'name' => $name);
      $people[$name]['adjacencies'] = array();
    }
    $to = preg_replace('/-.*$/','',$row['REPLYTOFABID']);
    if ($to != "") {
      if (!$people[$name]['adjacencies'][$to]) {
      	$people[$name]['adjacencies'][$to] = array('nodeTo' => $to, 'nodeFrom' => $name, 'data' => array('paperNum' => 0));
      }
      $num = $people[$name]['adjacencies'][$to]['data']['paperNum']++;
      $people[$name]['adjacencies'][$to]['data']['paperId$num'] = $row['RESOURCEID'];
    }
  }
  $adate = date('Y-m-d',strtotime("-30 days"));
  $stmt = DBExecute("SELECT person.username,COUNT(*) AS count
										 FROM person,reading 
										 WHERE person.username IN ('".implode("','",array_keys($people))."') 
										 AND reading.personid = person.id AND reading.date > ? GROUP BY person.username",$adate);
	while ($row = $stmt->fetchAssocRow()) {
		$people[$row['USERNAME']]['data']['$activeIndex'] += $row['COUNT'];
	}
	$stmt = DBExecute("SELECT person.username,a.ratebyanno,a.datecreated 
                       FROM annotation AS a,person 
                       WHERE a.personid = person.id 
                       AND person.username 
                       IN ('".implode("','",array_keys($people))."')");
	while ($row = $stmt->fetchAssocRow()) {
		$name = $row['USERNAME'];
		if($row['DATECREATED'] > $adate)
			$people[$name]['data']['$activeIndex']+=1;
		$people[$name]['data']['$paperNums']+=2;
		switch($row['RATEBYANNO']) {
			case 1:	$people[$name]['data']['$positive']++; break;
			case 2: $people[$name]['data']['$negative']++; break;
			case 3: $people[$name]['data']['$neutral']++; break;
			case 4: $people[$name]['data']['$positive']++; break;
		}
	}
  foreach($people as $a => $b) {
  	$b['adjacencies'] = array_values($b['adjacencies']);
  	array_push($data, $b);
  }
} elseif ($requestType == 'readPapers') {
	$stmt = DBExecute("SELECT DISTINCT reading.resourceid,bibtex.title,sa.datecreated FROM reading LEFT JOIN 
										 bibtex ON reading.resourceid=bibtex.id LEFT JOIN (
										 	SELECT annotation.resourceid,annotation.datecreated FROM annotation ORDER BY datecreated DESC
										 ) AS sa ON sa.resourceid=bibtex.id WHERE reading.personid=?",$id);
	$data = array();
	while($row = $stmt->fetchAssocRow()) {
		array_push($data, 
			array("id" => $row['RESOURCEID'],
						"title" => $row['TITLE'],
						"datemodified" => $row['DATECREATED']
			)
		);
	}
} elseif ($requestType == 'commentList') {
	$stmt = DBExecute("SELECT annotation.annotationbody,annotation.resourceid,bibtex.title,annotation.datecreated FROM annotation LEFT JOIN 
										 bibtex ON annotation.resourceid=bibtex.id WHERE annotation.personid=?",$id);
	$data = array();
	while($row = $stmt->fetchAssocRow()) {
		$title = (empty($row['TITLE'])) ? "paper ".$row['RESOURCEID'] : $row['TITLE'];
		array_push($data, 
			array("id" => $row['RESOURCEID'],
						"title" => $row['BODY'],
						"subtitle" => "Made on ".$row['DATECREATED']." on $title"
			)
		);
	}
}

if (isset($data)) {
	header('Content-type: application/json');
	echo json_encode($data);
} else {
	die('data generation failed');
}
?>